package Dao.Impl;

import Dao.UserDAO;
import Model.*;
import Util.DbConnector;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;

import java.sql.*;
import java.util.List;

public class UserDAOImpl implements UserDAO {
    private static final Logger logger = LogManager.getLogger(UserDAOImpl.class);
    private final QueryRunner queryRunner = new QueryRunner();

    public UserDAOImpl() {
    }

    @Override
    public void addUser(User user) {
        String sql = "INSERT INTO users(username, password, contactInfo) VALUES(?, ?, ?)";
        String[] columnNames = {"id"};
        try (Connection connection = DbConnector.getConnection();
             PreparedStatement stmt = connection.prepareStatement(sql, columnNames)) {
            stmt.setString(1, user.getUsername());
            stmt.setString(2, user.getPassword());
            stmt.setString(3, user.getContactInfo());
            int affectedRows = stmt.executeUpdate();

            if (affectedRows == 0) {
                throw new SQLException("创建用户失败，没有行受到影响。");
            }

            try (ResultSet generatedKeys = stmt.getGeneratedKeys()) {
                if (generatedKeys.next()) {
                    user.setId(generatedKeys.getInt(1));
                } else {
                    throw new SQLException("创建用户失败，没有获取到 ID！");
                }
            }
            logger.info("添加用户成功，用户名: {}", user.getUsername());
        } catch (SQLException e) {
            logger.error("添加用户失败，用户名: {}", user.getUsername(), e);
        }
    }

    @Override
    public void updateUser(User user) {
        String sql = "UPDATE users SET isFrozen = ?, password = ?, balance = ? WHERE username = ?";
        try (Connection connection = DbConnector.getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
            preparedStatement.setInt(1, user.isFrozen());
            preparedStatement.setString(2, user.getPassword());
            preparedStatement.setDouble(3, user.getBalance());
            preparedStatement.setString(4, user.getUsername());

            preparedStatement.executeUpdate();
            logger.info("更新用户成功，用户名: {}", user.getUsername());
        } catch (SQLException e) {
            logger.error("更新用户失败，用户名: {}", user.getUsername(), e);
        }
    }

    @Override
    public User getUsrByName(String name) {
        String sql = "SELECT * FROM users WHERE username = ?";
        try (Connection connection = DbConnector.getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
            preparedStatement.setString(1, name);

            ResultSet rs = preparedStatement.executeQuery();
            if (rs.next()) {
                User user = new User();
                user.setId(rs.getInt("id"));
                user.setUsername(rs.getString("username"));
                user.setPassword(rs.getString("password"));
                user.setContactInfo(rs.getString("contactInfo"));
                user.setBalance(rs.getDouble("balance"));
                user.setFrozen(rs.getInt("isFrozen"));
                return user;
            } else {
                return null;
            }
        } catch (SQLException e) {
            logger.error("获取用户失败，用户名: {}", name, e);
            return null;
        }
    }

    @Override
    public User getUserById(int id) {
        String sql = "SELECT * FROM users WHERE id = ?";
        try (Connection connection = DbConnector.getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
            preparedStatement.setInt(1, id);

            ResultSet rs = preparedStatement.executeQuery();
            if (rs.next()) {
                User user = new User();
                user.setId(rs.getInt("id"));
                user.setUsername(rs.getString("username"));
                user.setPassword(rs.getString("password"));
                user.setContactInfo(rs.getString("contactInfo"));
                user.setBalance(rs.getDouble("balance"));
                user.setFrozen(rs.getInt("isFrozen"));
                return user;
            } else {
                return null;
            }
        } catch (SQLException e) {
            logger.error("获取用户失败，用户ID: {}", id, e);
            return null;
        }
    }

    @Override
    public User[] getAllUser() {
        String sql = "SELECT * FROM users";
        try (Connection connection = DbConnector.getConnection()) {
            List<User> userList = queryRunner.query(connection, sql, new BeanListHandler<>(User.class));
            return userList.toArray(new User[0]);
        } catch (SQLException e) {
            logger.error("获取所有用户失败", e);
            return new User[0];
        }
    }

    @Override
    public void deleteUser(String username) {
        String sql = "DELETE FROM users WHERE username = ?";
        try (Connection connection = DbConnector.getConnection()) {
            queryRunner.update(connection, sql, username);
            logger.info("删除用户成功，用户名: {}", username);
        } catch (SQLException e) {
            logger.error("删除用户失败，用户名: {}", username, e);
        }
    }

    @Override
    public void addUserFinancialProduct(UserFinancialProduct userFinancialProduct) {
        String sql = "INSERT INTO UserFinancialProducts(userId, productId, purchaseAmount, status, expectedEarnings, purchaseDate) " +
                "VALUES(?, ?, ?, ?, ?, ?)";
        try (Connection connection = DbConnector.getConnection()) {
            Object[] params = {
                    userFinancialProduct.getUserId(),
                    userFinancialProduct.getProductId(),
                    userFinancialProduct.getPurchaseAmount(),
                    userFinancialProduct.getStatus(),
                    userFinancialProduct.getExpectedEarnings(),
                    new Timestamp(userFinancialProduct.getPurchaseDate().getTime())
            };
            queryRunner.update(connection, sql, params);
            logger.info("添加用户理财产品成功，用户ID: {}", userFinancialProduct.getUserId());
        } catch (SQLException e) {
            logger.error("添加用户理财产品失败，用户ID: {}", userFinancialProduct.getUserId(), e);
        }
    }

    @Override
    public List<UserFinancialProduct> getUserFinancialProducts(int userId) {
        String sql = "SELECT ufp.id, ufp.purchaseAmount, ufp.purchaseDate, ufp.status, ufp.expectedEarnings, " +
                "fp.id AS productId, fp.name AS productName, fp.description AS productDescription, fp.interestRate " +
                "FROM UserFinancialProducts ufp " +
                "JOIN FinancialProducts fp ON ufp.productId = fp.id " +
                "WHERE ufp.userId = ? AND fp.isActive = TRUE";
        try (Connection connection = DbConnector.getConnection()) {
            return queryRunner.query(connection, sql, new BeanListHandler<>(UserFinancialProduct.class), userId);
        } catch (SQLException e) {
            logger.error("获取用户理财产品失败，用户ID: {}", userId, e);
            return null;
        }
    }

    @Override
    public void updateUserFinancialProductStatus(UserFinancialProduct userFinancialProduct) {
        String sql = "UPDATE UserFinancialProducts SET status = ? WHERE id = ?";
        try (Connection connection = DbConnector.getConnection()) {
            queryRunner.update(connection, sql, userFinancialProduct.getStatus(), userFinancialProduct.getId());
            logger.info("更新用户理财产品状态成功，产品ID: {}", userFinancialProduct.getId());
        } catch (SQLException e) {
            logger.error("更新用户理财产品状态失败，产品ID: {}", userFinancialProduct.getId(), e);
        }
    }

    @Override
    public void deleteUserFinancialProduct(int id) {
        String sql = "DELETE FROM UserFinancialProducts WHERE id = ?";
        try (Connection connection = DbConnector.getConnection()) {
            queryRunner.update(connection, sql, id);
            logger.info("删除用户理财产品成功，产品ID: {}", id);
        } catch (SQLException e) {
            logger.error("删除用户理财产品失败，产品ID: {}", id, e);
        }
    }
}